package com.wanghang89.gmail.util.db;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Collections;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.apache.log4j.Logger;
import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.ResultSetExtractor;
import org.springframework.jdbc.core.RowCallbackHandler;
import org.springframework.jdbc.core.RowMapper;

import com.mysql.jdbc.log.Log;
import com.wanghang89.gmail.bean.AdministraRegion;
import com.wanghang89.gmail.bean.Url;

public class AdministraRegionDbUtils extends BaseDbUtils{
	
	private final static String FIELD_ID = "id";
	private final static String FIELD_NAME = "name";
	private final static String FIELD_FULL_SPELLING = "full_spelling";
	private final static String FIELD_SIMPLE_SPELLING = "simple_spelling";
	private final static String FIELD_REMARK = "remark";
	private final static String FIELD_SUPERIOR_ID = "superior_id";
	private final static String FIELD_GARDE_ID = "garde_id";
	private final static String FIELD_URL_ID = "url_id";
	
	

	private final static Logger LOGGER = Logger.getLogger(AdministraRegionDbUtils.class);
	private final static String SQL_SELECT_ALL = "select id,name,full_spelling,simple_spelling,remark,garde_id,superior_id,url_id from administra_region";
	private final static String SQL_SELECT_SUBORDINATE_ALL = SQL_SELECT_ALL+" where superior_id=?";
	private final static String SQL_ADD_ALL = "insert into administra_region(name,full_spelling,simple_spelling,remark,garde_id,superior_id)values(?,?,?,?,?,?)";
	private final static String SQL_UPDATE_SPELLING = "update administra_region set full_spelling=?,simple_spelling=? where id=?"; 
		
	private final static RowMapper<AdministraRegion> RM_ADMINISTRA_REGION = new RowMapper<AdministraRegion>() {
		@Override
		public AdministraRegion mapRow(ResultSet rs, int index)
				throws SQLException {
			LOGGER.debug("maprow index:"+index);
			AdministraRegion ar = new AdministraRegion();
			ar.setId(rs.getInt("id"));
			ar.setName(rs.getString("name"));
			ar.setFullSpelling(rs.getString("full_spelling"));
			ar.setSimpleSpelling(rs.getString("simple_spelling"));
			ar.setRemark(rs.getString("remark"));
			ar.setGardeId(rs.getInt("garde_id"));
			ar.setSuperiorId(rs.getInt("superior_id"));
			ar.setUrlId(rs.getInt("url_id"));
			return ar;
		}
	};
	
	private final static ResultSetExtractor<List<AdministraRegion>> ADMINISTRA_REGION_ARE = new ResultSetExtractor<List<AdministraRegion>>() {
						
		@Override
		public List<AdministraRegion> extractData(ResultSet rs) throws SQLException,
				DataAccessException {
			int size = 0;
			while (rs.next()) {
				size++;
			}
			rs.beforeFirst();
			if (size>0) {
				List<AdministraRegion> list = new ArrayList<AdministraRegion>(size);
				while (rs.next()) {
					AdministraRegion ar = new AdministraRegion();
					ar.setId(rs.getInt("id"));
					ar.setName(rs.getString("name"));
					ar.setFullSpelling(rs.getString("full_spelling"));
					ar.setSimpleSpelling(rs.getString("simple_spelling"));
					ar.setRemark(rs.getString("remark"));
					ar.setGardeId(rs.getInt("garde_id"));
					ar.setSuperiorId(rs.getInt("superior_id"));
					list.add(ar);
				}
				LOGGER.debug("query list size:"+size);
				return list;
			}
			return null;
		}
	};
	
	public AdministraRegionDbUtils()
	{
		
	}
	
	public Integer add(AdministraRegion ar) throws DBException
	{		
		Integer id = getId(ar);
		if (id>0) {
			return id;
		}
		Long new_id = add(SQL_ADD_ALL,convertToAddParams(ar));
		if (new_id!=-1) {
			return new_id.intValue(); 
		}
		return 0;
	}
	
	public boolean add(List<AdministraRegion> ars)
	{
		List<Object[]> params = new ArrayList<Object[]>();
		int size = ars.size();
		for (int i = 0; i < size; i++) {
			AdministraRegion ar = ars.get(i);
			Integer id = getId(ar);
			if (id<1) {
				params.add(convertToAddParams(ar));
			}
			ar = null;
		}		
		if (!params.isEmpty()) {
			jdbcTemplate.batchUpdate(SQL_ADD_ALL,params);
			params.clear();
		}
		params = null;
		return false;
	}
	
	private Object[] convertToAddParams(AdministraRegion ar)
	{
		return new Object[]{
				ar.getName()==null?"":ar.getName(),
				ar.getFullSpelling(),
				ar.getSimpleSpelling(),
				ar.getRemark()==null?"":ar.getRemark(),
				ar.getGardeId(),
				ar.getSuperiorId()
			};
	}
	
	public Integer getId(AdministraRegion ar)
	{
		StringBuilder builder = new StringBuilder();
		String name = ar.getName()==null?"":ar.getName();
		String remark = ar.getRemark()==null?"":ar.getRemark();
		Integer superiorId = ar.getSuperiorId();
		Integer gardeId = ar.getGardeId();
		builder.append("select id from administra_region where name=? and remark=? and garde_id=? and ")
				.append("superior_id ").append(superiorId!=null?"=?":"is null");
		int length = 3;
		if (superiorId!=null) {
			length++;
		}
		Object[] params = new Object[length];
		params[0] = name;
		params[1] = remark;
		params[2] = gardeId;
		if (length>3) {
			params[3] = superiorId;
		}
		String get_id_sql = builder.toString();
		Long id = jdbcTemplate.query(get_id_sql, params,GET_FIRST_LONG_EXTRACTOR);
		LOGGER.debug("get id:"+id);
		get_id_sql = null;
		params = null;
		
		return id.intValue();
	}
	
	public Map<Integer,String> getNoSpellingAdministraRegions(int fromId)
	{
		String sql = "select id , name from administra_region where full_spelling is null and id>? order by id asc limit 50 ";
		Map<Integer, String> map = jdbcTemplate.query(sql,new Object[]{fromId},new ResultSetExtractor<Map<Integer,String>>(){
			@Override
			public Map<Integer, String> extractData(ResultSet rs)
					throws SQLException, DataAccessException {
				Map<Integer,String> map = new HashMap<Integer, String>();
				while (rs.next()) {
					Integer id = rs.getInt(1);
					String name = rs.getString(2);
					LOGGER.debug("id:"+id+";name:"+name);
					map.put(id, name);
				}
				return map;
			}});
		if (!map.isEmpty()) {
			return map;
		}
		map = null;
		return null;
	}
	
	public boolean updateChineseSpelling(List<Object[]> ars)
	{		
		int[] affects = jdbcTemplate.batchUpdate(SQL_UPDATE_SPELLING,ars);
		if (LOGGER.isDebugEnabled()) {
			int length = affects.length;
			for (int i = 0; i < length; i++) {
				LOGGER.debug("affect:"+affects[i]);
			}
		}
		return true;
	}
	
	public AdministraRegion getByFuzzyNameAndGrade(String name , int type)
	{
		String pn = name+"%";
		String sql = SQL_SELECT_ALL+" where name like ? and garde_id=?";
		List<AdministraRegion> ars = jdbcTemplate.query(sql,new Object[]{pn,type},RM_ADMINISTRA_REGION);
		if (ars!=null&&!ars.isEmpty()) {
			LOGGER.debug("list size:"+ars.size());
			return ars.get(0);
		}
		return null;
	}
	
	public boolean updateUrlId(Integer arId , Integer urlId)
	{
		String sql = "update administra_region set url_id = ? where id=?";
		int affect = jdbcTemplate.update(sql,urlId,arId);
		LOGGER.debug("affect:"+affect);
		if (affect==1) {
			return true;
		}
		return false;
	}	
	
	public List<AdministraRegion> getAllSubordinateById(Integer id)
	{
		List<AdministraRegion> list = jdbcTemplate.query(SQL_SELECT_SUBORDINATE_ALL, new Object[]{id},RM_ADMINISTRA_REGION);
		if (list!=null) {
			int size = list.size();
			for (int i = 0; i < size ; i++) {
				AdministraRegion ar = list.get(i); 
				if (ar.getGardeId()<3) {
					list.addAll(getAllSubordinateById(ar.getId()));
				}
			}
		}
		list.removeAll(Collections.singleton(null));
		return list;
	}
	
	public List<AdministraRegion> getByGrade(Integer gradeId)
	{
		String sql = SQL_SELECT_ALL+" where garde_id=?";
		Object[] params = new Object[]{gradeId};
		List<AdministraRegion> ars = jdbcTemplate.query(sql,params,RM_ADMINISTRA_REGION);
		sql = null;
		params = null;
		if (ars==null) {
			return new ArrayList<AdministraRegion>();
		}
		return ars;
	}
	
	public static void main(String[] args) {
			
	}
	
}
